<?php
/**
 * @copyright Copyright (C) 2015-2017 AIZAWA Hina
 * @license https://github.com/fetus-hina/stat.ink/blob/master/LICENSE MIT
 * @author AIZAWA Hina <hina@fetus.jp>
 */

use app\components\db\Migration;

class m170812_123225_user_stat2_init extends Migration
{
    public function safeUp()
    {
        $select = file_get_contents(__FILE__, false, null, __COMPILER_HALT_OFFSET__);
        $insert = 'INSERT INTO "user_stat2" ' . trim($select) . ' ON CONFLICT DO NOTHING';
        $this->execute($insert);
    }

    public function safeDown()
    {
        $this->delete('user_stat2');
    }
}
__halt_compiler();
SELECT "battle2"."user_id", COUNT(*) AS "battles", SUM(CASE WHEN "battle2"."is_win" IS NULL THEN 0 ELSE 1 END) AS "have_win_lose", SUM(CASE WHEN "battle2"."is_win" IS NULL THEN 0 WHEN "battle2"."is_win" = TRUE THEN 1 ELSE 0 END) AS "win_battles", SUM(CASE WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 ELSE 1 END) AS "have_kill_death", SUM(CASE WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 ELSE "battle2"."kill" END) AS "kill", SUM(CASE WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 ELSE "battle2"."death" END) AS "death", SUM(CASE WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 WHEN "battle2"."start_at" IS NULL THEN 0 WHEN "battle2"."end_at" IS NULL THEN 0 ELSE 1 END) AS "have_kill_death_time", SUM(CASE WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 WHEN "battle2"."start_at" IS NULL THEN 0 WHEN "battle2"."end_at" IS NULL THEN 0 ELSE "battle2"."kill" END) AS "kill_with_time", SUM(CASE WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 WHEN "battle2"."start_at" IS NULL THEN 0 WHEN "battle2"."end_at" IS NULL THEN 0 ELSE "battle2"."death" END) AS "death_with_time", SUM(CASE WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 WHEN "battle2"."start_at" IS NULL THEN 0 WHEN "battle2"."end_at" IS NULL THEN 0 ELSE (EXTRACT(EPOCH FROM "battle2"."end_at") - EXTRACT(EPOCH FROM "battle2"."start_at")) END) AS "total_seconds", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'nawabari' THEN 0 ELSE 1 END) AS "turf_battles", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'nawabari' THEN 0 WHEN "battle2"."is_win" IS NULL THEN 0 ELSE 1 END) AS "turf_have_win_lose", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'nawabari' THEN 0 WHEN "battle2"."is_win" IS NULL THEN 0 WHEN "battle2"."is_win" = TRUE THEN 1 ELSE 0 END) AS "turf_win_battles", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'nawabari' THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 ELSE 1 END) AS "turf_have_kill_death", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'nawabari' THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 ELSE "battle2"."kill" END) AS "turf_kill", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'nawabari' THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 ELSE "battle2"."death" END) AS "turf_death", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'nawabari' THEN 0 WHEN "battle2"."is_win" IS NULL THEN 0 WHEN "battle2"."my_point" IS NULL THEN 0 ELSE 1 END) AS "turf_have_inked", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'nawabari' THEN 0 WHEN "battle2"."is_win" IS NULL THEN 0 WHEN "battle2"."my_point" IS NULL THEN 0 WHEN "battle2"."is_win" THEN "battle2"."my_point" - 1000 ELSE "battle2"."my_point" END) AS "turf_total_inked", MAX(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'nawabari' THEN 0 WHEN "battle2"."is_win" IS NULL THEN 0 WHEN "battle2"."my_point" IS NULL THEN 0 WHEN "battle2"."is_win" THEN "battle2"."my_point" - 1000 ELSE "battle2"."my_point" END) AS "turf_max_inked", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 ELSE 1 END) AS "gachi_battles", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 WHEN "battle2"."is_win" IS NULL THEN 0 ELSE 1 END) AS "gachi_have_win_lose", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 WHEN "battle2"."is_win" IS NULL THEN 0 WHEN "battle2"."is_win" = TRUE THEN 1 ELSE 0 END) AS "gachi_win_battles", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 ELSE 1 END) AS "gachi_have_kill_death", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 ELSE "battle2"."kill" END) AS "gachi_kill", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 ELSE "battle2"."death" END) AS "gachi_death", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 WHEN "battle2"."start_at" IS NULL THEN 0 WHEN "battle2"."end_at" IS NULL THEN 0 ELSE 1 END) AS "gachi_kill_death_time", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 WHEN "battle2"."start_at" IS NULL THEN 0 WHEN "battle2"."end_at" IS NULL THEN 0 ELSE "battle2"."kill" END) AS "gachi_kill_with_time", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 WHEN "battle2"."start_at" IS NULL THEN 0 WHEN "battle2"."end_at" IS NULL THEN 0 ELSE "battle2"."death" END) AS "gachi_death_with_time", SUM(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" NOT IN ('area', 'yagura', 'hoko') THEN 0 WHEN "battle2"."kill" IS NULL THEN 0 WHEN "battle2"."death" IS NULL THEN 0 WHEN "battle2"."start_at" IS NULL THEN 0 WHEN "battle2"."end_at" IS NULL THEN 0 ELSE (EXTRACT(EPOCH FROM "battle2"."end_at") - EXTRACT(EPOCH FROM "battle2"."start_at")) END) AS "gachi_total_seconds", MAX(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'area' THEN 0 WHEN "rank2a"."int_base" IS NULL AND "rank2b"."int_base" IS NULL THEN 0 ELSE GREATEST((CASE WHEN "rank2a"."int_base" IS NULL THEN 0 ELSE "rank2a"."int_base" END) + (CASE WHEN "rank2a"."int_base" IS NULL THEN 0 WHEN "battle2"."rank_exp" IS NULL THEN 0 ELSE "battle2"."rank_exp" END), (CASE WHEN "rank2b"."int_base" IS NULL THEN 0 ELSE "rank2b"."int_base" END) + (CASE WHEN "rank2b"."int_base" IS NULL THEN 0 WHEN "battle2"."rank_after_exp" IS NULL THEN 0 ELSE "battle2"."rank_after_exp" END)) END) AS "area_rank_peak", MAX(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'yagura' THEN 0 WHEN "rank2a"."int_base" IS NULL AND "rank2b"."int_base" IS NULL THEN 0 ELSE GREATEST((CASE WHEN "rank2a"."int_base" IS NULL THEN 0 ELSE "rank2a"."int_base" END) + (CASE WHEN "rank2a"."int_base" IS NULL THEN 0 WHEN "battle2"."rank_exp" IS NULL THEN 0 ELSE "battle2"."rank_exp" END), (CASE WHEN "rank2b"."int_base" IS NULL THEN 0 ELSE "rank2b"."int_base" END) + (CASE WHEN "rank2b"."int_base" IS NULL THEN 0 WHEN "battle2"."rank_after_exp" IS NULL THEN 0 ELSE "battle2"."rank_after_exp" END)) END) AS "yagura_rank_peak", MAX(CASE WHEN "lobby2"."key" = 'private' THEN 0 WHEN "mode2"."key" = 'private' THEN 0 WHEN "rule2"."key" <> 'hoko' THEN 0 WHEN "rank2a"."int_base" IS NULL AND "rank2b"."int_base" IS NULL THEN 0 ELSE GREATEST((CASE WHEN "rank2a"."int_base" IS NULL THEN 0 ELSE "rank2a"."int_base" END) + (CASE WHEN "rank2a"."int_base" IS NULL THEN 0 WHEN "battle2"."rank_exp" IS NULL THEN 0 ELSE "battle2"."rank_exp" END), (CASE WHEN "rank2b"."int_base" IS NULL THEN 0 ELSE "rank2b"."int_base" END) + (CASE WHEN "rank2b"."int_base" IS NULL THEN 0 WHEN "battle2"."rank_after_exp" IS NULL THEN 0 ELSE "battle2"."rank_after_exp" END)) END) AS "hoko_rank_peak", NOW() AS "updated_at" FROM "battle2" LEFT JOIN "lobby2" ON "battle2"."lobby_id" = "lobby2"."id" LEFT JOIN "mode2" ON "battle2"."mode_id" = "mode2"."id" LEFT JOIN "rule2" ON "battle2"."rule_id" = "rule2"."id" LEFT JOIN "rank2" "rank2a" ON "battle2"."rank_id" = "rank2a"."id" LEFT JOIN "rank2" "rank2b" ON "battle2"."rank_after_id" = "rank2b"."id" GROUP BY "battle2"."user_id"
